<?php
/*
 * [JINYUN!] (C)2001-2099 Jinyunweb.com
 * This is NOT a freeware, use is subject to license terms
 * $Id: 2017-12-13 03:39:35 apple $
*/
defined('BY_JYA') or exit('error');
define('PDO_DEBUG', true);

class DB {
	private $pdo;
	private $tablepre;
	protected $result;
	protected $statement;
	protected $errors = array();
	private function getPDO() {
		return $this->pdo;
	}
	public function __construct($config=array()){
		if(!empty($config)){
			$this->config=$config;
			$this->connect($config);
		}
		if($this->pdo==null){
			exit('error pdo');
		}
	}
	private function connect($cfg){
		
		$this->tablepre = $cfg['tablepre'];
		if($cfg['replace']){
			$this->replace=str_split($cfg['replace']);
		}
		if(empty($cfg)) {
			exi("The master database is not found!",'error');
		}
		$dsn = "mysql:dbname={$cfg['database']};host={$cfg['host']};port={$cfg['port']}";
		$dbclass = '';
		$options = array();
		if (class_exists('PDO')) {
			if (extension_loaded("pdo_mysql") && in_array('mysql', PDO::getAvailableDrivers())) {
				$dbclass = 'PDO';
				$options = array(
					PDO::ATTR_PERSISTENT => $cfg['pconnect'],
				);
				
			} else {
				if(!class_exists('_PDO')) {
					include ROOT_D . '/core/library/pdo/PDO.class.php';
				}
				$dbclass = '_PDO';
			}
		} else {
			include ROOT_D . '/core/library/pdo/PDO.class.php';
			$dbclass = 'PDO';
		}
		$this->pdo = new $dbclass($dsn, $cfg['username'], $cfg['password'], $options);
			$sql = "SET NAMES '{$cfg['charset']}';";
			$this->pdo->exec($sql);
			$this->pdo->exec("SET sql_mode='';");
			if(PDO_DEBUG) {
				$info = array();
				$info['sql'] = $sql;
				$info['error'] = $this->pdo->errorInfo();
				$this->debug(false, $info);
			}
	}

	private function prepare($sql) {
		$statement = $this->pdo->prepare($sql);
		return $statement;
	}
	
	
	public function query($sql, $params = array()) {
		$starttime = microtime();
		if (empty($params)) {
			$result = $this->pdo->exec($sql);
			if(PDO_DEBUG) {
				$info = array();
				$info['sql'] = $sql;
				$info['error'] = $this->pdo->errorInfo();
				$this->debug(false, $info);
			}
			return $result;
		}
		$statement = $this->prepare($sql);
		$result = $statement->execute($params);
		if(PDO_DEBUG) {
			$info = array();
			$info['sql'] = $sql;
			$info['params'] = $params;
			$info['error'] = $statement->errorInfo();
			$this->debug(false, $info);
		}
		$endtime = microtime();
		$this->performance($sql, $endtime - $starttime);
		if (!$result) {
			return false;
		} else {
			return $statement->rowCount();
		}
	}

	
	public function fetchcolumn($sql, $params = array(), $column = 0) {
		if($params){
			$params=$this->check($params);
		}
		$starttime = microtime();
		$statement = $this->prepare($sql);
		$result = $statement->execute($params);
		if(PDO_DEBUG) {
			$info = array();
			$info['sql'] = $sql;
			$info['params'] = $params;
			$info['error'] = $statement->errorInfo();
			$this->debug(false, $info);
		}
		$endtime = microtime();
		$this->performance($sql, $endtime - $starttime);
		if (!$result) {
			return false;
		} else {
			$temp= $statement->fetchColumn($column);
			$temp=$this->back($temp);
			return $temp;
		}
	}
	
	
	public function fetch($sql, $params = array()) {
		if($params){
			$params=$this->check($params);
		}
		$starttime = microtime();
		$statement = $this->prepare($sql);
		$result = $statement->execute($params);
		if(PDO_DEBUG) {
			$info = array();
			$info['sql'] = $sql;
			$info['params'] = $params;
			$info['error'] = $statement->errorInfo();
			$this->debug(false, $info);
		}
		$endtime = microtime();
		$this->performance($sql, $endtime - $starttime);
		if (!$result) {
			return false;
		} else {
			$temp= $statement->fetch(pdo::FETCH_ASSOC);
			$temp=$this->back($temp);
			if(!$temp){
				$temp=array();
			}
			return $temp;
		}
	}

	
	public function fetchall($sql, $params = array(), $keyfield = '') {
		if($params){
			$params=$this->check($params);
		}
		$starttime = microtime();
		$statement = $this->prepare($sql);
		$result = $statement->execute($params);
		if(PDO_DEBUG) {
			$info = array();
			$info['sql'] = $sql;
			$info['params'] = $params;
			$info['error'] = $statement->errorInfo();
			$this->debug(false, $info);
		}
		$endtime = microtime();
		$this->performance($sql, $endtime - $starttime);
		if (!$result) {
			return false;
		} else {
			if (empty($keyfield)) {
				$temp= $statement->fetchAll(pdo::FETCH_ASSOC);
				$temp=$this->back($temp);
				if(!$temp){
					$temp=array();
				}
				return $temp;
			} else {
				$temp = $statement->fetchAll(pdo::FETCH_ASSOC);
				$temp=$this->back($temp);
				$rs = array();
				if (!empty($temp)) {
					foreach ($temp as $key => &$row) {
						if (isset($row[$keyfield])) {
							$rs[$row[$keyfield]] = $row;
						} else {
							$rs[] = $row;
						}
					}
				}
				return $rs;
			}
		}
	}
	
	public function get($tablename, $params = array(), $fields = array(),$orderby='') {
		$this->main_tablename=$tablename;
		$select = '*';
		$join_sql='';
		if (!empty($fields)){
			$select=$this->check_fields($fields,$join_sql);
		}
		$condition = $this->implode($params, 'AND');
		$orderby=$orderby?" ORDER BY {$orderby}":'';
		$sql = "SELECT {$select} FROM " . $this->tablename($tablename).$join_sql . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '') . "{$orderby} LIMIT 1";
		return $this->fetch($sql, $condition['params']);
	}
	public function check($params){
		//return $params;
		foreach($params as &$row){
			//给单引号和反斜杠添加反斜杠转义
			$row=str_replace(array('\\',"'"),array('\\\\',"\'"),$row);
		}
		return $params;
	}
	public function back($data){
		//return $data;
		if(!$data){
			return $data;
		}
		if(!is_array($data)){
			$data=stripslashes($data);
			return $data;
		}else{
			foreach($data as &$row){
				$row=$this->back($row);
			}
			return $data;
		}
	}
	private function check_fields($fields,&$join_sql=null){
		if(!$fields){
			return false;
		}
		if(!is_array($fields)){
			$fields=array($fields);
		}
		$join=false;
		foreach($fields as $row){
			if(is_array($row)){
				$join=true;
				break;
			}
		}
		$join_sql='';
		foreach($fields as &$field){
			//join_sql 拼接较为复杂，后期有空再设计
			
			if(is_array($field)){
				if(!$field['table'] || !$field['join'] || !$field['fields']){
					
					exi('数据库语句参数错误！','error');
				}
				
				$tablename=$this->tablename($field['table']);
				$join_sql.=" LEFT JOIN {$tablename} AS {$field['table']} ON {$field['table']}.{$field['join']['0']}={$this->main_tablename}.{$field['join']['1']}";
				
				foreach($field['fields'] as &$f){
					$f="{$field['table']}.{$f}";
				}
				$field=implode(",",$field['fields']);
			}elseif(strexists($field,' ')){
				$temp=explode(' ',$field);
				if($temp['1']=='count'){
					$field='count(*) as `'.$temp['0'].'`';
				}elseif($temp['1']=='sum'){
					//如果未设置第三项，则以字段名作为结果字段
					$temp['2']=$temp['2']?$temp['2']:$temp['0'];
					$field='sum(`'.$temp['0'].'`) as `'.$temp['2'].'`';
				}else{
					exi('数据库语句参数错误！','error');
				}
			}else{
				if($join){
					$field="{$this->main_tablename}.{$field}";
				}else{
					$field="`{$field}`";
				}
				
			}
		}
		if($join_sql){
			$join_sql=' AS '.$this->main_tablename.$join_sql;
		}
		$result=implode(',',$fields);
		return $result;
	}
	/*
		综合型列表数据获取函数，参数说明
		$params=array(
			'table'=>'表名',
			'params'=>'筛选条件数组',
			'fields'=>'查询字段',
			'key'=>'指定键名字段',
			'limit'=>'获取数据条数',
			'orderby'=>'指定排序方式（支持字符串和数组两种形式）',
			'groupby'=>'分组',
		);
	*/
	public function getlist($params,&$total=null){
		$this->main_tablename=$params['table'];
		$select_sql='*';
		$join_sql='';
		if (!empty($params['fields'])){
			
			$select_sql=$this->check_fields($params['fields'],$join_sql);
		}
		$condition = $this->implode($params['params'], 'AND');
		$orderby_sql='';
		if (!empty($params['orderby'])) {
			if (is_array($params['orderby'])){
				if(!$params['orderby']['0']){
					foreach($params['orderby'] as $ok=>&$ov){
						$ov ="{$ok} {$ov}";
					}
				}
				$orderby_sql = implode(',', $params['orderby']);
			}else{
				$orderby_sql = $params['orderby'];
			}
			$orderby_sql=' ORDER BY '.$orderby_sql;
		}
		$groupby_sql='';
		if($params['groupby']){
			$groupby_sql=' group by '.$params['groupby'];
		}
		$limit_sql='';
		if (!empty($params['limit'])) {
			if (is_array($params['limit'])) {
				$limit_sql = " LIMIT " . ($params['limit'][0] - 1) * $params['limit'][1] . ', ' . $params['limit'][1];
			} else {
				$limit_sql = strexists(strtoupper($params['limit']), 'LIMIT') ? " {$params['limit']} " : " LIMIT {$params['limit']}";
			}
		}
		
		if($total!==null){
			$total = $this->fetchcolumn("SELECT COUNT(*) FROM " . $this->tablename($params['table']) . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : ''), $condition['params']);
		}
		$sql = "SELECT {$select_sql} FROM " . $this->tablename($params['table']) .$join_sql. (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '') .$groupby_sql.$orderby_sql.$limit_sql;
		return $this->fetchall($sql, $condition['params'], $params['key']);
	}
	public function getall($tablename, $params = array(), $fields = array(), $keyfield = '', $orderby = array()) {
		$this->main_tablename=$tablename;
		$select = '*';
		$join_sql='';
		if (!empty($fields)){
			$select=$this->check_fields($fields,$join_sql);
		}
		$condition = $this->implode($params, 'AND');
		if (!empty($orderby)) {
			if (is_array($orderby)) {
				$orderbysql = implode(',', $orderby);
			} else {
				$orderbysql = $orderby;
			}
		}
		
		$sql = "SELECT {$select} FROM " .$this->tablename($tablename) .$join_sql. (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '') . (!empty($orderbysql) ? " ORDER BY $orderbysql " : '') . $limitsql;
		
		return $this->fetchall($sql, $condition['params'], $keyfield);
	}
	public function get_next_id($tablename){
		$sql="SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='{$this->config['database']}' AND TABLE_NAME='{$this->config['tablepre']}{$tablename}'";
		$res=$this->fetch($sql);
		return $res['auto_increment'];
	}
	public function getsum($tablename, $params = array(), $fieldname = '') {
		
		$select = 'SUM(`'.$fieldname.'`) as `'.$fieldname.'`';
		if (empty($fieldname)){
			return false;
		}
		$condition = $this->implode($params, 'AND');
		
		$sql = "SELECT {$select} FROM " .$this->tablename($tablename) . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '') . " LIMIT 1";
		$res=$this->fetch($sql, $condition['params']);
		return $res[$fieldname];
	}
	public function getslice($tablename, $params = array(), $limit = array(), &$total = null, $fields = array(), $keyfield = '', $orderby = array(),$groupby='') {
		$this->main_tablename=$tablename;
		$select = '*';
		$join_sql='';
		if (!empty($fields)){
			$select=$this->check_fields($fields,$join_sql);
		}
		$condition = $this->implode($params, 'AND');
		if (!empty($limit)) {
			if (is_array($limit)) {
				$limitsql = " LIMIT " . ($limit[0] - 1) * $limit[1] . ', ' . $limit[1];
			} else {
				$limitsql = strexists(strtoupper($limit), 'LIMIT') ? " $limit " : " LIMIT $limit";
			}
		}
		
		if (!empty($orderby)) {
			if (is_array($orderby)) {
				$orderbysql = implode(',', $orderby);
			} else {
				$orderbysql = $orderby;
			}
		}
		if ($groupby && !strexists($groupby,' ')) {
			$groupbysql=' group by `'.$groupby.'` ';
		}else{
			$groupbysql='';
		}
		$sql = "SELECT {$select} FROM " . $this->tablename($tablename) .$join_sql. (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '') .$groupbysql. (!empty($orderbysql) ? " ORDER BY $orderbysql " : '') . $limitsql;
		$total = $this->fetchcolumn("SELECT COUNT(*) FROM " . tablename($tablename) . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : ''), $condition['params']);
		return $this->fetchall($sql, $condition['params'], $keyfield);
	}
	
	public function getcolumn($tablename, $params = array(), $field) {
		$result = $this->get($tablename, $params, array($field));
		if (!empty($result)) {
			return $result[$field];
		} else {
			return false;
		}
	}
	public function count($tablename,$params,$groupby=''){
		$condition = $this->implode($params, 'AND');
		if($groupby){
			$groupby=' group by `'.$groupby.'`';
		}else{
			$groupby='';
		}
		
		if($groupby){
			$total=$this->fetchall("SELECT COUNT(*) FROM " . tablename($tablename) . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '').$groupby, $condition['params']);
			$total=count($total);
		}else{
			$total = $this->fetchcolumn("SELECT COUNT(*) FROM " . tablename($tablename) . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '').$groupby, $condition['params']);
		}
		return $total?:0;
	}
	public function update($table, $data = array(), $params = array(), $glue = 'AND') {
		if($data){
			$data=$this->check($data);
		}
		if($params){
			$params=$this->check($params);
		}
		$fields = $this->implode($data, ',');
		$condition = $this->implode($params, $glue);
		$params = array_merge($fields['params'], $condition['params']);
		$sql = "UPDATE " . $this->tablename($table) . " SET {$fields['fields']}";
		$sql .= $condition['fields'] ? ' WHERE '.$condition['fields'] : '';
		return $this->query($sql, $params);
	}

	
	public function insert($table, $data = array(), $replace = FALSE) {
		if($data){
			$data=$this->check($data);
		}
		$cmd = $replace ? 'REPLACE INTO' : 'INSERT INTO';
		$condition = $this->implode($data, ',');
		return $this->query("$cmd " . $this->tablename($table) . " SET {$condition['fields']}", $condition['params']);
	}
	
	
	public function insertid() {
		return $this->pdo->lastInsertId();
	}

	
	public function delete($table, $params = array(), $glue = 'AND') {
		if($params){
			$params=$this->check($params);
		}
		$condition = $this->implode($params, $glue);
		$sql = "DELETE FROM " . $this->tablename($table);
		$sql .= $condition['fields'] ? ' WHERE '.$condition['fields'] : '';
		return $this->query($sql, $condition['params']);
	}

	
	public function begin() {
		$this->pdo->beginTransaction();
	}

	
	public function commit() {
		$this->pdo->commit();
	}

	
	public function rollback() {
		$this->pdo->rollBack();
	}

	
	private function implode($params, $glue = ',') {
		$result = array('fields' => ' 1 ', 'params' => array());
		$split = '';
		$suffix = '';
		$allow_operator = array('>', '<', '<>', '!=', '>=', '<=', '+=', '-=', 'LIKE', 'like','likes','between','BETWEEN','FIND_IN_SET','OR');
		if (in_array(strtolower($glue), array('and', 'or'))) {
			$suffix = '__';
		}
		if (!is_array($params)) {
			$result['fields'] = $params;
			return $result;
		}
		
		if (is_array($params)) {
			$result['fields'] = array();
			foreach ($params as $fields => $value) {
				$operator = '';
				if (strpos($fields, ' ') !== FALSE) {
					list($fields, $operator) = explode(' ', $fields, 2);
					if (!in_array($operator, $allow_operator)) {
						$operator = '';
					}
				}
				if (empty($operator)) {
					$fields = trim($fields);
					if (is_array($value)) {
						
						$operator = 'IN';
					} else {
						$operator = '=';
					}
				} elseif ($operator == '+=') {
					$operator = " = `$fields` + ";
				} elseif ($operator == '-=') {
					$operator = " = `$fields` - ";
				} elseif ($operator == '!=') {
					if(is_array($value)){
						$operator = "NOT IN";
					}
					
				} elseif ($operator == 'FIND_IN_SET') {
					
					
				}
				if (is_array($value)) {
					$insql = array();
					foreach ($value as $k => $v) {
						$insql[] = ":{$suffix}{$fields}_{$k}";
						if($operator=='likes'){
							$v="%{$v}%";
						}
						$result['params'][":{$suffix}{$fields}_{$k}"] = is_null($v) ? '' : $v;
					}
					if($operator=='between'){
						$result['fields'] []= "`$fields` {$operator} ".implode(" and ", $insql);
					}elseif($operator=='likes'){
						foreach($insql as $ins){
							$result['fields'] []= "`{$fields}` like {$ins}";
						}
					}else{
						$result['fields'] []= "`$fields` {$operator} (".implode(",", $insql).")";
					}
					
					
				} else {
					if($operator=='like' && !strexists($v,'%')){
						
						$v="%{$v}%";
					}
					if($result['params'][":{$suffix}$fields"]){
						$result['fields'][]= "`$fields` {$operator}  :{$suffix}{$fields}1";
						$result['params'][":{$suffix}{$fields}1"] = is_null($value) ? '' : $value;
					}elseif($operator == 'FIND_IN_SET'){
						$result['fields'][]=  " FIND_IN_SET(`$fields` ,:{$suffix}$fields )";
						$result['params'][":{$suffix}$fields"] = is_null($value) ? '' : str_replace(';',',',trim($value,';'));
					}elseif($operator == 'OR'){
						$result['fields'][]=  '('.$value.')';
					}else{
						$result['fields'][]=  "`$fields` {$operator}  :{$suffix}$fields";
						$result['params'][":{$suffix}$fields"] = is_null($value) ? '' : $value;
					}
				}
			}
			$split = ' ' . $glue . ' ';
			$result['fields'] =implode($split,$result['fields']);
		}
		return $result;
	}
	
	
	public function run($sql, $stuff = 'ims_') {
		if (!isset($sql) || empty($sql)) {
			return;
		}
		$sql = str_replace("\r", "\n", str_replace(' ' . $stuff, ' ' . $this->tablepre, $sql));
		$sql = str_replace("\r", "\n", str_replace(' `' . $stuff, ' `' . $this->tablepre, $sql));
		$ret = array();
		$num = 0;
		$sql = preg_replace("/\;[ \f\t\v]+/", ';', $sql);
		foreach(explode(";\n", trim($sql)) as $query) {
			$ret[$num] = '';
			$queries = explode("\n", trim($query));
			foreach($queries as $query) {
				$ret[$num] .= (isset($query[0]) && $query[0] == '#') || (isset($query[1]) && isset($query[1]) && $query[0].$query[1] == '--') ? '' : $query;
			}
			$num++;
		}
		unset($sql);
		foreach($ret as $query) {
			$query = trim($query);
			if($query) {
				$this->query($query, array());
			}
		}
	}
	
	
	public function fieldexists($tablename, $fieldname) {
		$isexists = $this->fetch("DESCRIBE " . $this->tablename($tablename) . " `{$fieldname}`", array());
		return !empty($isexists) ? true : false;
	}
	
	
	public function indexexists($tablename, $indexname) {
		if (!empty($indexname)) {
			$indexs = $this->fetchall("SHOW INDEX FROM " . $this->tablename($tablename), array(), '');
			if (!empty($indexs) && is_array($indexs)) {
				foreach ($indexs as $row) {
					if ($row['Key_name'] == $indexname) {
						return true;
					}
				}
			}
		}
		return false;
	}
	
	
	public function tablename($table) {
		return '`'.$this->tablepre.$table.'`';
	}

	
	public function debug($output = true, $append = array()) {
		if(!empty($append)) {
			$output = false;
			array_push($this->errors, $append);
		}
		if($output) {
			//print_r($this->errors);
		} else {
			if (!empty($append['error'][1])) {
				$traces = debug_backtrace();
				$ts = '';
				foreach($traces as $trace) {
					$trace['file'] = str_replace('\\', '/', $trace['file']);
					$trace['file'] = str_replace(ROOT_D, '', $trace['file']);
					$ts .= "file: {$trace['file']}; line: {$trace['line']}; <br />";
				}
				$params = var_export($append['params'], true);
				$message="SQL: <br/>{$append['sql']}<hr/>Params: <br/>{$params}<hr/>SQL Error: <br/>{$append['error'][2]}<hr/>Traces: <br/>{$ts}";
				log_error($message,3);
				if(defined('INSTALL') || DEVELOP){
					exi('执行数据库查询出错！'.$message,'alert');
				}
			}
		}
		return $this->errors;
	}

	
	public function tableexists($table) {
		if(!empty($table)) {
			$data = $this->fetch("SHOW TABLES LIKE '".$this->tablepre.$table."'", array());
			if(!empty($data)) {
				$data = array_values($data);
				$tablename = $this->tablepre.$table;
				if(in_array($tablename, $data)) {
					return true;
				} else {
					return false;
				}
			} else {
				return false;
			}
		} else {
			return false;
		}
	}
	
	private function performance($sql, $runtime = 0) {
		global $_AR;
		if(!$_AR['system']['maxtimesql']){
			return false;
		}
		if ($runtime == 0) {
			return false;
		}
		if (strpos($sql, 'core_performance')>0) {
			return false;
		}
		if ($runtime > $_AR['system']['maxtimesql']) {
			$sqldata = array(
				'runtime' => $runtime,
				'runurl' => 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'],
				'runsql' => $sql,
				'createtime' => TIMESTAMP
			);
			$this->insert('core_performance', $sqldata);
		}
		return true;
	}
}
